package com.kaizeli.website.service.impl;

import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelReader;
import com.kaizeli.website.service.DynamicImportService;
import com.kaizeli.website.util.FieldMappingUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 动态导入服务实现
 * 支持Excel表头自动匹配数据库字段
 */
@Slf4j
@Service
public class DynamicImportServiceImpl implements DynamicImportService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    private String tableType;
    
    /**
     * 设置表类型
     */
    public void setTableType(String type) {
        this.tableType = type;
    }
    
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Map<String, Object> dynamicImport(MultipartFile file) {
        return processImport(file, false);
    }
    
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Map<String, Object> dynamicImportWithUpdate(MultipartFile file) {
        return processImport(file, true);
    }
    
    @Override
    public byte[] downloadTemplate() {
        // 生成Excel模板
        return new byte[0];
    }
    
    @Override
    public Map<String, Object> getImportHelp() {
        Map<String, Object> help = new HashMap<>();
        help.put("title", "智能导入说明");
        help.put("description", "支持根据Excel表头自动匹配字段，无需固定列顺序");
        help.put("fieldDescriptions", FieldMappingUtil.getFieldDescriptions(tableType));
        help.put("notes", Arrays.asList(
            "1. 第一行为表头（中文列名）",
            "2. 支持灵活的列顺序",
            "3. 系统自动匹配字段",
            "4. 支持多种同义名称"
        ));
        return help;
    }
    
    /**
     * 处理导入
     */
    private Map<String, Object> processImport(MultipartFile file, boolean updateMode) {
        Map<String, Object> result = new HashMap<>();
        
        // 校验文件
        if (file == null || file.isEmpty()) {
            result.put("success", false);
            result.put("message", "文件为空");
            return result;
        }
        
        String filename = file.getOriginalFilename();
        if (filename == null || (!filename.endsWith(".xlsx") && !filename.endsWith(".xls"))) {
            result.put("success", false);
            result.put("message", "仅支持Excel文件");
            return result;
        }
        
        int successCount = 0;
        int updateCount = 0;
        int failCount = 0;
        List<String> errorMessages = new ArrayList<>();
        
        try (InputStream inputStream = file.getInputStream()) {
            ExcelReader reader = ExcelUtil.getReader(inputStream, 0);
            List<List<Object>> allRows = reader.read();
            
            if (allRows == null || allRows.isEmpty()) {
                result.put("success", false);
                result.put("message", "Excel文件为空");
                return result;
            }
            
            // 第一行是表头
            List<Object> headers = allRows.get(0);
            Map<String, Integer> fieldMapping = new HashMap<>();
            
            // 映射表头
            for (int i = 0; i < headers.size(); i++) {
                String header = headers.get(i) != null ? headers.get(i).toString().trim() : "";
                String dbField = FieldMappingUtil.mapToDatabaseField(tableType, header);
                if (dbField != null && !fieldMapping.containsKey(dbField)) {
                    fieldMapping.put(dbField, i);
                }
            }
            
            log.info("表头: {}", headers);
            log.info("字段映射: {}", fieldMapping);
            
            if (fieldMapping.isEmpty()) {
                result.put("success", false);
                result.put("message", "未能识别任何有效字段");
                return result;
            }
            
            // 处理数据行
            for (int rowIndex = 1; rowIndex < allRows.size(); rowIndex++) {
                List<Object> row = allRows.get(rowIndex);
                if (row == null || row.isEmpty()) continue;
                
                try {
                    Map<String, Object> rowData = buildRowData(row, fieldMapping);
                    if (insertOrUpdateData(rowData, updateMode)) {
                        updateCount++;
                    } else {
                        successCount++;
                    }
                } catch (Exception e) {
                    log.error("处理第{}行数据失败", rowIndex + 1, e);
                    errorMessages.add("第" + (rowIndex + 1) + "行: " + e.getMessage());
                    failCount++;
                }
            }
            
            result.put("success", true);
            result.put("total", allRows.size() - 1);
            result.put("successCount", successCount);
            result.put("updateCount", updateCount);
            result.put("failCount", failCount);
            result.put("errors", errorMessages);
            result.put("message", String.format("导入完成：成功 %d 条，更新 %d 条，失败 %d 条", 
                successCount, updateCount, failCount));
            
        } catch (Exception e) {
            log.error("导入失败", e);
            result.put("success", false);
            result.put("message", "导入失败: " + e.getMessage());
        }
        
        return result;
    }
    
    /**
     * 构建行数据
     */
    private Map<String, Object> buildRowData(List<Object> row, Map<String, Integer> fieldMapping) {
        Map<String, Object> rowData = new HashMap<>();
        for (Map.Entry<String, Integer> entry : fieldMapping.entrySet()) {
            String fieldName = entry.getKey();
            int colIndex = entry.getValue();
            if (colIndex < row.size()) {
                Object value = row.get(colIndex);
                if (value != null && !value.toString().trim().isEmpty()) {
                    rowData.put(fieldName, value);
                }
            }
        }
        return rowData;
    }
    
    /**
     * 插入或更新数据
     */
    private boolean insertOrUpdateData(Map<String, Object> rowData, boolean updateMode) {
        try {
            if ("employee".equalsIgnoreCase(tableType)) {
                return handleEmployeeData(rowData, updateMode);
            } else if ("project".equalsIgnoreCase(tableType)) {
                return handleProjectData(rowData, updateMode);
            } else if ("contract_personnel".equalsIgnoreCase(tableType)) {
                return handleContractPersonnelData(rowData, updateMode);
            }
        } catch (Exception e) {
            log.error("插入数据失败", e);
            throw e;
        }
        return false;
    }
    
    /**
     * 处理员工数据
     */
    private boolean handleEmployeeData(Map<String, Object> rowData, boolean updateMode) {
        String idCard = (String) rowData.get("id_card");
        if (idCard == null || idCard.trim().isEmpty()) {
            throw new RuntimeException("身份证号不能为空");
        }
        
        // 检查是否已存在
        String checkSql = "SELECT COUNT(*) FROM employee WHERE id_card = ?";
        Integer count = jdbcTemplate.queryForObject(checkSql, Integer.class, idCard);
        boolean exists = count != null && count > 0;
        
        if (exists && updateMode) {
            // 更新模式
            String updateSql = buildUpdateSql("employee", rowData, "id_card = ?");
            jdbcTemplate.update(updateSql, prepareParams(rowData, idCard));
            return true;
        } else if (!exists) {
            // 新增
            String insertSql = buildInsertSql("employee", rowData);
            jdbcTemplate.update(insertSql, prepareParams(rowData));
            return false;
        }
        return false;
    }
    
    /**
     * 处理项目数据
     */
    private boolean handleProjectData(Map<String, Object> rowData, boolean updateMode) {
        String projectCode = (String) rowData.get("project_code");
        if (projectCode == null || projectCode.trim().isEmpty()) {
            throw new RuntimeException("项目编码不能为空");
        }
        
        String checkSql = "SELECT COUNT(*) FROM project_basic_info WHERE project_code = ?";
        Integer count = jdbcTemplate.queryForObject(checkSql, Integer.class, projectCode);
        boolean exists = count != null && count > 0;
        
        if (exists && updateMode) {
            String updateSql = buildUpdateSql("project_basic_info", rowData, "project_code = ?");
            jdbcTemplate.update(updateSql, prepareParams(rowData, projectCode));
            return true;
        } else if (!exists) {
            String insertSql = buildInsertSql("project_basic_info", rowData);
            jdbcTemplate.update(insertSql, prepareParams(rowData));
            return false;
        }
        return false;
    }
    
    /**
     * 处理合同人员数据
     */
    private boolean handleContractPersonnelData(Map<String, Object> rowData, boolean updateMode) {
        String projectCode = (String) rowData.get("project_code");
        String personnelName = (String) rowData.get("personnel_name");
        
        if (projectCode == null || projectCode.trim().isEmpty()) {
            throw new RuntimeException("项目编号不能为空");
        }
        if (personnelName == null || personnelName.trim().isEmpty()) {
            throw new RuntimeException("人员姓名不能为空");
        }
        
        String checkSql = "SELECT COUNT(*) FROM contract_personnel WHERE project_code = ? AND personnel_name = ?";
        Integer count = jdbcTemplate.queryForObject(checkSql, Integer.class, projectCode, personnelName);
        boolean exists = count != null && count > 0;
        
        if (exists && updateMode) {
            String updateSql = buildUpdateSql("contract_personnel", rowData, "project_code = ? AND personnel_name = ?");
            jdbcTemplate.update(updateSql, prepareParams(rowData, projectCode, personnelName));
            return true;
        } else if (!exists) {
            String insertSql = buildInsertSql("contract_personnel", rowData);
            jdbcTemplate.update(insertSql, prepareParams(rowData));
            return false;
        }
        return false;
    }
    
    /**
     * 构建INSERT SQL
     */
    private String buildInsertSql(String tableName, Map<String, Object> data) {
        List<String> fields = new ArrayList<>(data.keySet());
        String fieldsStr = String.join(", ", fields);
        String placeholders = String.join(", ", Collections.nCopies(fields.size(), "?"));
        return String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, fieldsStr, placeholders);
    }
    
    /**
     * 构建UPDATE SQL
     */
    private String buildUpdateSql(String tableName, Map<String, Object> data, String whereClause) {
        List<String> updateFields = data.keySet().stream()
            .map(f -> f + " = ?")
            .collect(Collectors.toList());
        String setClause = String.join(", ", updateFields);
        return String.format("UPDATE %s SET %s WHERE %s", tableName, setClause, whereClause);
    }
    
    /**
     * 准备参数
     */
    private Object[] prepareParams(Map<String, Object> data, Object... whereParams) {
        List<Object> params = new ArrayList<>(data.values());
        if (whereParams != null) {
            for (Object param : whereParams) {
                params.add(param);
            }
        }
        return params.toArray();
    }
}

